/****** Object:  StoredProcedure [dbo].[SBO_SP_TransactionNotification]    Script Date: 11/05/2013 11:18:54 ******/
ALTER proc [dbo].[SBO_SP_TransactionNotification] 

@object_type nvarchar(20), 				-- SBO Object Type
@transaction_type nchar(1),			-- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
@num_of_cols_in_key int,
@list_of_key_cols_tab_del nvarchar(255),
@list_of_cols_val_tab_del nvarchar(255)

AS

begin

-- Return values
declare @error  int				-- Result (0 for no error)
declare @error_message nvarchar (200) 		-- Error string to be displayed
select @error = 0
select @error_message = N'Ok'

--------------------------------------------------------------------------------------------------------------------------------
If @transaction_type = N'A' 
Begin
	
	Declare @DocEntry nvarchar(100)
	Declare @CustomerCode nvarchar(20)
	Declare @ProjectCode nvarchar(50)
	Declare @Invoice table (CardCode nvarchar(20) , PrjCode nvarchar(50))
	Declare @Outgoing table (DocEntry int, CardCode nvarchar(20), PrjCode nvarchar(50))
	Declare @Journal table (DocEntry int, PrjCode nvarchar(50))
	If @object_type = N'46' and @num_of_cols_in_key = 1
	begin
		
		
		Set @CustomerCode = (Select CardCode from OVPM where DocEntry  = @list_of_cols_val_tab_del)
		Set @ProjectCode = (Select IsNull(PrjCode,'') from OVPM where DocEntry  = @list_of_cols_val_tab_del)
		
		If @ProjectCode <> ''
		Begin
			If (Select COUNT(DocEntry) from OVPM where CardCode = @CustomerCode and PrjCode = @ProjectCode and Canceled <> 'Y') > 1
			begin
				set @error = -1
				set @error_message = 'Duplicate Contract number / Project code, please enter different Contract number / project code'
			end
		End
	end
	Else If @object_type = N'24' and @num_of_cols_in_key = 1
	begin
		set @CustomerCode = (select CardCode from ORCT where DocEntry  = @list_of_cols_val_tab_del)		
		set @ProjectCode = (Select IsNull(PrjCode,'') from ORCT where DocEntry  = @list_of_cols_val_tab_del)
		if @ProjectCode = ''
		begin
			set @error = -1
			set @error_message = 'Prject Code could not be blank.'			
		end
		else
		begin
			set @DocEntry = (select top 1 DocEntry from InterestOVPM where CardCode = @CustomerCode and ProjectCode = @ProjectCode)
			insert into @Invoice	select
										T1.CardCode
										,T1.Project  
									from 
										ORCT T 	
										left join RCT2 T0 on T0.DocNum = T.DocNum
										left join OINV T1 on T0.DocEntry = T1.DocEntry
									where T.DocEntry = @list_of_cols_val_tab_del and T0.InvType = 13
			
			If (select COUNT(*) from @Invoice where CardCode <> @CustomerCode or PrjCode <> @ProjectCode) > 0
			begin
				set @error = -1
				set @error_message = 'AR Invoice have different project code with Incoming Payment'
				 
			end
			
			insert into @Outgoing	select
										T1.DocEntry	
										,T1.CardCode
										,T1.PrjCode  
									from 
										ORCT T 	
										left join RCT2 T0 on T0.DocNum = T.DocNum
										left join OVPM T1 on T0.DocEntry = T1.TransId
									where T.DocEntry = @list_of_cols_val_tab_del and T0.InvType = 46
			If (select COUNT(*) from @Outgoing where CardCode <> @CustomerCode or PrjCode <> @ProjectCode or DocEntry <> @DocEntry) > 0
			begin
				set @error = -1
				set @error_message = 'Ougoing payment have different project code with Incoming Payment'
				 
			end
			
			insert into @Journal	select
										T1.Ref3											
										,T1.Project  
									from 
										ORCT T 	
										left join RCT2 T0 on T0.DocNum = T.DocNum
										left join OJDT T1 on T0.DocEntry = T1.TransId
									where T.DocEntry = @list_of_cols_val_tab_del and T0.InvType = 30
			If (select COUNT(*) from @Journal where PrjCode <> @ProjectCode or DocEntry <> @DocEntry) > 0
			begin
				set @error = -1
				set @error_message = 'Journal Entry have different project code with Incoming Payment'				 
			end
		end
		
	end
End
--------------------------------------------------------------------------------------------------------------------------------

-- Select the return values
select @error, @error_message

end